﻿// GUI API calls to redraw or build elements to be inserted
function html_to_sql(str) {
    // first replace the breaks with newlines
    str = str.replace(/<br\/?>/gi, '\n');
    // grab all remainer <tags> and remove them    
    var regex = /<\/?([A-Z][A-Z0-9]*)[^>]*>/gi
    str = str.replace(regex, '');
    str = str.replace(/&nbsp;/g, ' ');
    str = str.replace(/&gt;/g, '>');
    str = str.replace(/&lt;/g, '<');
    return str;
}

// precondition, the esc_sql_to_html should be ran first otherwise you'll show tags
function sql_to_html(str) {
    // some syntax chars that should be translated
    str = str.replace(/</g, '&lt;').replace(/>/g, '&gt;');      
    // keywords
    var regex = /\badd\b|\ball\b|\band\b|\bany\b|\bas\b|\basc\b|\bbetween |\bby\b|\bcase\b|\bconvert\b|\bcreate\b|\bcurrent\b|\bcurrent_date\b|\bcurrent_time\b|\bcurrent_timestamp\b|\bdelete\b|\bdesc\b|\bdistinct\b|\bdrop\b|\bdump\b|\belse\b|\bend\b|\bescape\b|\bexec\b|\bexists\b|\bfrom |\bgroup\b| identity\b|\bif\b|\bin |insert\b|\bintersect\b|\binto\b|\bis\b|\bjoin\b|\bkey\b|\bleft\b|\blike\b|\bnot\b|\bnull\b|\bnullif\b|\bof\b|\boff\b|\boffsets\b|\bon\b|\bor |\border\b| outer\b|\bpercent\b|\bright\b|\browcount\b|\bselect\b|\bset\b|\bsome\b|\bstatistics\b|\btable\b|\bthen\b|\bto\b|\btop\b|\bunion\b|\bunique\b|\bupdate\b|\buse\b|\bvalues\b|\bview\b|\bwhen\b|\bwhere\b|\bwhile\b|\bwith\b/gi
    str = str.replace(regex, colorize_blue);
    // functions
    // date and time functions
    regex = /(\bdateadd\b|\bdatediff\b|\bdatename\b|\bdatepart\b|\bday\b|\bgetdate\b|\bgetutcdate\b|\bmonth\b|\byear)\(.*?\)\b/gi
    str = str.replace(regex, colorize_violet);
    // math functions
    regex = /(\babs\b|\bacos\b|\basin\b|\batan\b|\batn2\b|\bceiling\b|\bcos\b|\bcot\b|\bdegrees\b|\bexp\b|\bfloor\b|\blog\b|\blog10\b|\bpi\b|\bpower\b|\bradians\b|\brand\b|\bround\b|\bsign\b|\bsin\b|\bsquare\b|\bsqrt\b|\btan\b)\(.*?\)/gi
    str = str.replace(regex, colorize_violet);
    // string functions
    regex = /(\bascii\b|\bchar\b|\bcharindex\b|\bdifference\b|\bleft\b|\blen\b|\blower\b|\bltrim\b|\bnchar\b|\bpatindex\b|\breplace\b|\bquotename\b|\breplicate\b|\breverse\b|\bright\b|\brtrim\b|\bsoundex\b|\bspace\b|\bstr\b|\bstuff\b|\bsubstring\b|\bunicode\b|\bupper\b)\(.*?\)/gi
    str = str.replace(regex, colorize_violet);
    // aggregate functions
    regex = /(\bavg\b|\bbinary_checksum\b|\bchecksum\b|\bchecksum_agg\b|\bcount\b|\bcount_big\b|\bgrouping\b|\bmax\b|\bmin\b|\bsum\b|\bstdev\b|\bstdevp\b|\bvar\b|\bvarp\b)\(.*?\)/gi
    str = str.replace(regex, colorize_violet);
    // user text
    regex = /\'.*[^\']*\'/gi
    str = str.replace(regex, colorize_red);
    // put page breaks in new lines
    str = str.replace(/\n/g, function(a) { return '<br/>' });      // keep the newline, even though it wont be shown
    // whenever there is a string of white spaces, replace it with a &nbsp;
    str = str.replace(/\s\s/g, function(a) { return '&nbsp;' });   
    return str;
}

function colorize_blue(str) {
    return '<span style="color: blue">' + str + '</span>';
}

function colorize_red(str) {
    return '<span style="color: red">' + str+ '</span>';
}

function colorize_violet(str) {
    // colorize a function, do the func( ), but not the inside
    var indexstart = str.indexOf('(') + 1;
    var indexend = str.lastIndexOf(')');
    return '<span style="color: #ee82ee">' + str.substring(0, indexstart) + '</span>' +
        str.substring(indexstart, indexend) +
        '<span style="color: #ee82ee">' + str.substring(indexend, indexend + 1) + '</span>';    
}

// note this c# tag evaluates even though intellisense does not work
function onLoad() {
    reselectList();
}

// TODO pass sql code instead of pulling from list
function loadSqlDiv() {
    var sqlvar = getSqlVar(getSelectedVal());
    if (sqlvar != null) {
        $id('sqlDiv').innerHTML = sql_to_html(sqlvar.sql_code);
        $id('inputAltQuery').disabled = false;
        $id('inputCopyToSql').disabled = false;
    }
}


function reselectList() {
    var select = $id('querySelectList');    
    var curItem = $id('create_query_short_desc_input').value;

    for (var i = 0; i < select.options.length; i++) {
        var option = select.options[i];
        if (option.text == curItem) {
            select.selectedIndex = i;
            var sqlVar = getSqlVar(option.value);           
            break;
        }
    }

    if (select.selectedIndex >= 0) {
        loadSqlDiv();
    }
}

function repopulateSqlList() {
    var selectE = $id('querySelectList');
    //var lastSelectedVal = getSelectedVal();
    var currentShortDesc = $id('create_query_short_desc_input').value;
    // clear list
    selectE.innerHTML = "";    

    /// rebuild list
    for (var x in sql_store) {
        var can = sql_store[x];
        var opt = document.createElement("option");
        var shortDescTrim = can.short_desc;
        opt.text = shortDescTrim;
        opt.value = can.short_desc;
        if (can.short_desc == currentShortDesc) {
            opt.selected = true;
        }        
        selectE.add(opt, null);        
    }
}

// API To get JSON objects via ids
function getSelectedVal() {
    var selectE = $id('querySelectList');
    var index = selectE.selectedIndex;
    if (index < 0) {
        return;
    }
    return selectE.options[index].value;
}

// get the json wqe description 
function getSqlVar(short_desc) {
    if (!sql_store) return null;
    for (var x in sql_store) {
        if (sql_store[x].short_desc == short_desc) {
            return sql_store[x];
        }
    }
    return null;
}

/////////////////////////////////////
// event handlers for browser click functions
//////////////////////////////////
function clickShowCreateDesc(visible) {
    $id('create_query_div').style.display = (visible ? "block" : "none");
    $id('select_query_div').style.display = (!visible ? "block" : "none");
}

function clickLoadSqlVar() {
    var sqlvar = getSqlVar(getSelectedVal());
    if (sqlvar != null) {
        $id('create_query_short_desc_input').value = sqlvar.short_desc;
        $id('create_query_long_desc_input').value = sqlvar.long_desc;
        loadSqlDiv();   
    }
}

function clickCopyToSQL(executeQuery) {
    $id('sqlTextBox').value = html_to_sql($id('sqlDiv').innerHTML);
    if (executeQuery) {
        $id('serverForm').submit();
    }
}

function clickDropSqlVar(sql_id) {
    alert('todo drop: ' + sql_id);
}

function clickSaveToCSV() {
    // couple tricks here, we change our webpage to sqloutput.aspx and append the URL params
    var sql_code = $id('sqlTextBox').value;
    var output_type = "csv";

    var url = location.href;
    url = url.substr(0, url.lastIndexOf('/')) + '/sqloutput.aspx?';
    url += "output_type=" + output_type;
    url += "&sql_code=" + escape(sql_code);
    
    location.href = url;

}

function clickSaveToView() {
    // couple tricks here, we change our webpage to sqloutput.aspx and append the URL params
    var sql_code = $id('sqlTextBox').value;    

    var url = location.href;
    url = url.substr(0, url.lastIndexOf('/')) + '/ViewDesigner.aspx?';
    url += "view_name=" + escape($id('create_query_short_desc_input').value.trim());
    url += "&sql_query=" + escape(sql_code);    

    location.href = url;

}

function clickSaveSqlVar() {
    var shortDesc = $id('create_query_short_desc_input').value.trim();
    var longDesc = $id('create_query_long_desc_input').value;
    var sqlCode = $id('sqlTextBox').value;

    if (shortDesc.length < 1) {
        alert("unable to save query, short description invalid");
    }
    if (shortDesc.length > 50) {
        alert("unable to save uery, short description too long (50 char max)");
    }

    ajaxSaveSqlVar(shortDesc, longDesc, sqlCode,
    function(json) {
        repopulateSqlList();
        reloadSqlDiv();
    });
}

function clickDeleteSqlVar() {
    var shortDesc = $id('create_query_short_desc_input').value;
    ajaxDeleteSqlVar(shortDesc, function(suc) {
        if (suc) {
            repopulateSqlList();
        }
    });
}



//////////////////
/// AJAX callbacks
////////////////
function ajaxSaveSqlVar(shortDesc, longDesc, sqlCode, retFunc) {
    Proxy.ajaxSaveSqlVar(shortDesc, longDesc, sqlCode,
        function(result) {
            var json = toJSON(result);
            var oldVal = getSqlVar(json.short_desc);
            if (oldVal == null) {
                sql_store.push(json);
            }
            // update the internal node
            else {
                oldVal.sql_code = json.sql_code;
                oldVal.long_desc = json.long_desc;
            }
            retFunc(json);
        },
        onError
       );
}

function ajaxDeleteSqlVar(shortDesc, retFunc) {
    Proxy.ajaxDeleteSqlVar(shortDesc,
    function(result) {
        var json = toJSON(result);
        // should evaluate to true if successful, false otherwise
        if (json) {
            for (var x in sql_store) {
                if (sql_store[x].short_desc == shortDesc) {
                    delete sql_store[x];
                    break;
                }
            }
        }
        else {
            alert('unable to delete query');
        }
        retFunc(json);
    },
    onError
   );
}

function onError(error) {
    // show error message for 4 seconds highlighted
    showStatus("Error: " + error.Message, 4000, true);
}

///////////////////////
//// UTILITY FUNCTIONS
/////////////////////

// get the document.getElementById shortname
function $id(id) { return document.getElementById(id); }

// turn json text into a json object
function toJSON(text) { return (new Function("return " + text))() }

String.prototype.trim = function() { return this.replace(/^\screen+|\screen+$/, '') }